JDBC:(JAVA DataBase Connectivity) java 連接資料庫的標準
屬於ORM操作SQL資料庫
JDBC每次進行資料庫連結後處理SQL最後關閉資料庫。容易出資料庫連結忘記關閉的人工錯誤。導致連線被占用降低效能。為減少這樣的錯誤。JDBCtemplate就被設計出來。
JDBCtemplate是對JDBC的封裝,可以自動完成JDBC底層工作,比喻如下:資料庫等於倉庫,而JDBC是普通鐵門倉庫,而JDBCtemplate是自動門的倉庫,讓我們這些懶惰的管理者可以進出不用忘了關門。
Starter依賴設定,以mySQL為案例
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
資料庫連線application.properties,
//IP、編碼、時區、SSL
spring.datasource.url=jdbc:mysql://127.0.0.1/book?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=true
//使用者名稱、密碼
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#新增實體類別
@Data
public class User implements RowMapper<User> {
private int id;
private String username;
private String password;
// 連結資料表與model實體欄位 : : mapRow方法
@Override
public User mapRow(ResultSet resultSet, int i) throws SQLException {
User user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
return user;
}
#操作資料表
execute: directly doing SQL Laungue
updat: insert, update, change value and save it, delete those you don't want... etc operate.
query: find or list what you want to know, query some data for user from the Database.
@RestController
@RequestMapping("user")
public class UserController {
@Autowired
private JdbcTemplate jdbcTemplate;
//創建資料表
@GetMapping("createUserTable")
public String createUserTable() throws Exception {
String sql = "CREATE TABLE `user` (\n" +
" `id` int(10) NOT NULL AUTO_INCREMENT,\n" +
" `username` varchar(100) DEFAULT NULL,\n" +
" `password` varchar(100) DEFAULT NULL,\n" +
" PRIMARY KEY (`id`)\n" +
") ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;\n" +
"\n";
jdbcTemplate.execute(sql);
return "success creat the table";
}
#增加資料update
@GetMapping("saveUserTest")
public String saveUserTest()throws Exception {
String sql = "INSERT INTO user (USERNAME,PASSWORD) VALUES ('jasonwu','9453')";
int rows = jdbcTemplate.update(sql);
return "执行成功,影响" + rows + "行";
}
#查詢單一資料
@GetMapping("getUserByName")
public List getUserByName(String userName)throws Exception {
String sql = "SELECT * FROM user WHERE USERNAME = ?";
List<User> list = jdbcTemplate.query(sql, new User(), new Object[]{userName});
return list;
}
#修改使用者密碼
@GetMapping("updateUserPassword")
public String updateUserPassword(int id, String passWord) throws Exception {
String sql = "UPDATE user SET PASSWORD = ? WHERE ID = ?";
int rows = jdbcTemplate.update(sql, passWord, id);
return "邪惡的修改了密碼第" + rows + "行";
}
#刪除資料
@GetMapping("deleteUserById")
public String deleteUserById(int id) throws Exception {
String sql = "DELETE FROM user WHERE ID = ?";
int rows = jdbcTemplate.update(sql, id);
return "這位大大你終於刪掉使用者資料" + rows + "行";
}
雖然多數人用JPA和MYBATIS但是,JDBCtemplate很好學,因為學習成本低,會一點SQL就可以出來唬爛
,仍然很多人在用。操作雖然麻煩但是一下就上手了喔!!!!